﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
namespace BookStore.Admin.Models
{
    public class UsersModel
    {
        private string connectionString;
        private SqlConnection connect;

        public UsersModel()
        {
            connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;
            connect = new SqlConnection(connectionString);
            
        }
        public int Add_Account(string []account)
        {
            int result = 0;
            int result_email = 0;

            try
            {
               
                connect.Open();

                string selectSQL = "SELECT count(*) as total FROM Users WHERE user_name = '" + account[0]+"'";
                SqlCommand cmd = new SqlCommand(selectSQL, connect);

                SqlDataReader reader;
                reader = cmd.ExecuteReader();
                if (reader.Read())
                    result = Convert.ToInt32(reader["total"]);
                reader.Close();
                if (result != 0) return -1;

                string selectSQL_email = "SELECT count(*) as total FROM Users WHERE email = '" + account[3]+"'";
                SqlCommand cmd_email = new SqlCommand(selectSQL_email, connect);

                SqlDataReader reader_email;
                reader_email = cmd_email.ExecuteReader();
                if (reader_email.Read())
                    result_email = Convert.ToInt32(reader_email["total"]);
                reader_email.Close();
                if (result_email != 0) return -2;

                selectSQL = "INSERT INTO Users (user_code,user_name,password,email,phone,address,cmnd,created_date,updated_date,active,active_code,user_type) VALUES ('" + account[0] + "','" + account[0] + "','" + account[1] + "','" + account[3] + "','" + account[5] + "',N'" + account[2] + "','" + account[4] + "'," + account[7] + "," + account[8] + "," + account[9] + ",'" + account[10] + "',"+account[11]+")";
                cmd = new SqlCommand(selectSQL, connect);
                cmd.ExecuteNonQuery();
            }
            catch (Exception err)
            {
                return -3;
            }
            finally
            {
                connect.Close();
            }

            return 1;
           
        }
        public int Edit_Account(string type, int user_id, string[] account)
        {
            int result_email = 0;
            try
            {

                connect.Open();
                string selectSQL_email = "SELECT count(*) as total FROM Users WHERE email = '" + account[3] + "' and user_id !=" + user_id;
                SqlCommand cmd_email = new SqlCommand(selectSQL_email, connect);

                SqlDataReader reader_email;
                reader_email = cmd_email.ExecuteReader();
                if (reader_email.Read())
                    result_email = Convert.ToInt32(reader_email["total"]);
                reader_email.Close();
                if (result_email >=1) return -2;

                string selectSQL = "UPDATE Users SET phone='" + account[1] + "',address = N'" + account[0] + "',cmnd='" + account[2] + "',email='" + account[3] + "',updated_date=" + account[4];
                if (account[5] != null)
                    selectSQL += ",password='" + account[5]+"'";
                if (account[6] != null)
                    selectSQL += ",active=" + account[6];
                if (account[7] != null)
                    selectSQL += ",user_type=" + account[7];
                if(type=="1")
                    selectSQL += " where user_id=" + user_id;
                else
                    selectSQL += " where user_id=" + user_id + " and user_type=3";
                SqlCommand cmd = new SqlCommand(selectSQL, connect);
                cmd.ExecuteNonQuery();
            }
            catch (Exception err)
            {
                return -3;
            }
            finally
            {
                connect.Close();
            }

            return 1;
        }
        

        public int Check_User(string user)
        {
            int result = 0;
            try
            {
                connect.Open();
                string selectSQL = "SELECT user_id,active FROM Users WHERE user_name = '" + user + "'";
                SqlCommand cmd = new SqlCommand(selectSQL, connect);
                SqlDataReader reader;
                reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    result = Convert.ToInt32(reader["user_id"]);
                }
                else
                    result = -1;
                reader.Close();

                if (result == -1) return -1;
                else return result;
            }
            catch (Exception err)
            {
                return -1;
            }
            finally
            {
                connect.Close();
            }
        }
        public int Check_Pass(string pass1,int user_id)
        {
             int result = 0;
             string pass = null;
             int active = 0;
             int type = 0;
            try
            {
                connect.Open();
                string selectSQL = "SELECT *  FROM Users WHERE user_id = " + user_id;
                SqlCommand cmd = new SqlCommand(selectSQL, connect);
                SqlDataReader reader;
                reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    pass = reader["password"].ToString();
                    active = Convert.ToInt32(reader["active"].ToString());
                    type = Convert.ToInt32(reader["user_type"].ToString());
                    if (pass == pass1)
                        result = 1;
                    else
                        return -1;

                }
                reader.Close();

                if (result == -1) return -1;
                else if (active == 0) return -2;
                else if (type == 3) return -3;
                else if (active == 2) return -4;
                else return 1;
            }
            catch (Exception err)
            {
                return -1;
            }
            finally
            {
                connect.Close();
            }
        }
        public int Change_Password(int user_id,string old, string new_)
        {
            int result = 0;
            string pass=null;
            try
            {
                connect.Open();
                string selectSQL = "SELECT *  FROM Users WHERE user_id = " + user_id;
                SqlCommand cmd = new SqlCommand(selectSQL, connect);
                SqlDataReader reader;
                reader = cmd.ExecuteReader();
                if (reader.Read())
                {    pass = reader["password"].ToString();
                    if(pass==old)
                        result = 1;
                    else
                         result = -1;

                }
                reader.Close();
                if (result == -1 || result == 0) return -1;
                else
                {

                    string selectSQL_update = "UPDATE Users set password='" + new_ + "' where user_id=" + user_id + "";
                    SqlCommand cmd_update = new SqlCommand(selectSQL_update, connect);
                    cmd_update.ExecuteNonQuery();
                }
                
            }
            catch (Exception err)
            {
                return -1;
            }
            finally
            {
                connect.Close();
            }
         return 1;
        }


        public int Change_Pass(string user_name,string pass )
        {
            try
            {
                connect.Open();
                string selectSQL_update = "UPDATE Users set password='" + pass + "' where user_name='" + user_name + "'";
                    SqlCommand cmd_update = new SqlCommand(selectSQL_update, connect);
                    cmd_update.ExecuteNonQuery();

            }
            catch (Exception err)
            {
                return -1;
            }
            finally
            {
                connect.Close();
            }
         return 1;
        }

        public string[] getDetailUserById(int ID)
        {
            string[] result = new string[8];
            try
            {
                connect.Open();
                string selectSQL = "SELECT email,phone,address,cmnd,user_name,user_type,active FROM Users WHERE user_id = " + ID.ToString();
                SqlCommand cmd = new SqlCommand(selectSQL, connect);

                SqlDataReader reader;
                reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    result[0] = reader["email"].ToString();
                    result[1] = reader["phone"].ToString();
                    result[2] = reader["cmnd"].ToString();
                    result[3] = reader["address"].ToString();
                    result[4] = reader["user_name"].ToString();
                    result[5] = reader["active"].ToString();
                    result[6] = reader["user_type"].ToString();
                    result[7] = reader["user_type"].ToString();
                }

                reader.Close();
            }
            catch (Exception err)
            {
                return null;
            }
            finally
            {
                connect.Close();
            }

            return result;
        }

        public string getUserNamebyID(int ID)
        {
            string result = "";
            try
            {
                connect.Open();
                string selectSQL = "SELECT user_name FROM Users WHERE user_id = " + ID.ToString();
                SqlCommand cmd = new SqlCommand(selectSQL, connect);

                SqlDataReader reader;
                reader = cmd.ExecuteReader();
                if (reader.Read())
                    result = reader["user_name"].ToString();

                reader.Close();
            }
            catch (Exception err)
            {
                return null;
            }
            finally
            {
                connect.Close();
            }

            return result;
        }

        public int Check_User_Email(string username, string email)
        {
            int result = 0;
            int result_email = 0;
            try
            {
                connect.Open();

                string selectSQL = "SELECT count(*) as total FROM Users WHERE user_name = '" + username + "'";
                SqlCommand cmd = new SqlCommand(selectSQL, connect);

                SqlDataReader reader;
                reader = cmd.ExecuteReader();
                if (reader.Read())
                    result = Convert.ToInt32(reader["total"]);
                reader.Close();
                if (result == 0 || result == -1) return -1;

                string selectSQL_email = "SELECT count(*) as total FROM Users WHERE email = '" + email + "'";
                SqlCommand cmd_email = new SqlCommand(selectSQL_email, connect);

                SqlDataReader reader_email;
                reader_email = cmd_email.ExecuteReader();
                if (reader_email.Read())
                    result_email = Convert.ToInt32(reader_email["total"]);
                reader_email.Close();
                if (result_email == 0 || result_email == -1) return -1;

               
            }
            catch (Exception err)
            {
                return -3;
            }
            finally
            {
                connect.Close();
            }

            return 1;
           
        }
        public string[][] getListUser(string type,int user_id)
        {
            string[][] result = new string[100][];
            try
            {
                string active = "";
                string user_type = "";
                connect.Open();
                string selectSQL ;
                if(type =="1")
                     selectSQL = "SELECT * FROM Users WHERE user_id != " + user_id;
                else
                    selectSQL = "SELECT * FROM Users WHERE user_id != " + user_id+ " and user_type=3";

                SqlCommand cmd = new SqlCommand(selectSQL, connect);

                SqlDataReader reader;
                reader = cmd.ExecuteReader();
                int i = 0;
                while (reader.Read())
                {
                    if (reader["active"].ToString() == "1")
                        active = "Enable";
                    else
                        active = "Disable";

                    if (reader["user_type"].ToString() == "1")
                        user_type = "Administrator";
                    else if (reader["user_type"].ToString() == "2")
                        user_type = "Modarator";
                    else
                        user_type = "User";
                    result[i] = new string[6];
                    result[i][0] = reader["user_id"].ToString();
                    result[i][1] = reader["user_name"].ToString();
                    result[i][2] = active;
                    result[i][3] = new DateTime(1970, 1, 1).AddSeconds(Convert.ToInt32(reader["created_date"].ToString())).ToString("dd-MM-yyyy hh:mm");
                    result[i][4] = new DateTime(1970, 1, 1).AddSeconds(Convert.ToInt32(reader["updated_date"].ToString())).ToString("dd-MM-yyyy hh:mm");
                    result[i][5] = user_type;
                    i++;
                }
                reader.Close();
            }
            catch (Exception err)
            {
                return null;
            }
            finally
            {
                connect.Close();
            }

            return result;
        }
        public string DeleteUserByID(string type,int id)
        {
            try
            {
                connect.Open();
                string selectSQL_1;
                string[] bill_id = null;
                string[] bill_rent_id = null;
                 
                // xoa favroite cua user 
                selectSQL_1 = "delete FROM Favorite_Book WHERE user_id =" + id;
                SqlCommand cmd_1 = new SqlCommand(selectSQL_1, connect);
                cmd_1.ExecuteNonQuery();

                // xoa comment cua user 
                selectSQL_1 = "delete FROM Comment WHERE user_id =" + id;
                cmd_1 = new SqlCommand(selectSQL_1, connect);
                cmd_1.ExecuteNonQuery();

                // tim nhung bill cua user
                selectSQL_1 = "select * from Bill where user_id=" + id;
                cmd_1 = new SqlCommand(selectSQL_1, connect);
                SqlDataReader reader;
                reader = cmd_1.ExecuteReader();
                int i = 0;
                while (reader.Read())
                {
                    bill_id[i] = reader["bill_id"].ToString();
                    i++;
                }
                reader.Close();

                // neu co detail thi xoa detail
                if (bill_id != null)
                {
                    foreach (string bdid in bill_id)
                    {
                        selectSQL_1 = "delete FROM Bill_Detail WHERE bill_id =" + bdid;
                        cmd_1 = new SqlCommand(selectSQL_1, connect);
                        cmd_1.ExecuteNonQuery();
                    }
                } 
                // xoa bill
                selectSQL_1 = "delete FROM Bill WHERE user_id =" + id;
                cmd_1 = new SqlCommand(selectSQL_1, connect);
                cmd_1.ExecuteNonQuery();

               // tim bill rent cua user
                selectSQL_1 = "select * from Bill_Rent where user_id=" + id;
                cmd_1 = new SqlCommand(selectSQL_1, connect);
                reader = cmd_1.ExecuteReader();

                int j=0;
                while (reader.Read())
                {
                    bill_rent_id[j] = reader["bill_id"].ToString();
                    j++;
                }
                reader.Close();

                // neu co detail thi xoa detail
                if (bill_rent_id != null)
                {
                    foreach (string bdetail_id in bill_rent_id)
                    {
                        selectSQL_1 = "delete FROM Bill_Rent_Detail WHERE bill_rent_id =" + bdetail_id;
                        cmd_1 = new SqlCommand(selectSQL_1, connect);
                        cmd_1.ExecuteNonQuery();
                    }
                } 


                // xoa bill rent
                selectSQL_1 = "delete FROM Bill_Rent WHERE user_id =" + id;
                cmd_1 = new SqlCommand(selectSQL_1, connect);
                cmd_1.ExecuteNonQuery();
                
                // xoa user
                if(type=="1")
                    selectSQL_1 = "delete FROM Users WHERE user_id =" + id;
                else
                    selectSQL_1 = "delete FROM Users WHERE user_id =" + id +" and user_type =3";
                cmd_1 = new SqlCommand(selectSQL_1, connect);
                cmd_1.ExecuteNonQuery();

            }
            catch (Exception err)
            {
                return "-1";
            }
            finally
            {
                connect.Close();
            }
            return "1";
        }
        public string CheckAccount(int id)
        {
            string type_user = "";
            try
            {
                connect.Open();
                string selectSQL_1;
                selectSQL_1 = "select * from Users where user_id=" + id;
                SqlCommand cmd_1 = new SqlCommand(selectSQL_1, connect);
                SqlDataReader reader;
                reader = cmd_1.ExecuteReader();

                if (reader.Read())
                    type_user = Convert.ToInt32(reader["user_type"]).ToString();

                reader.Close();
            }
            catch (Exception err)
            {
                return "-1";
            }
            finally
            {
                connect.Close();
            }
            return type_user;
        }

        public string getTotalUser(int tu,int den)
        {
            string type_user = "";
            try
            {
                connect.Open();
                string selectSQL_1="";
                if (tu != 0 && den != 0)
                {
                    selectSQL_1 = "select Count(*) as total from Users where created_date between 0 and " + den;
                }
                else
                    selectSQL_1 = "select Count(*) as total from Users";
                
                SqlCommand cmd_1 = new SqlCommand(selectSQL_1, connect);
                SqlDataReader reader;
                reader = cmd_1.ExecuteReader();

                if (reader.Read())
                    type_user = Convert.ToInt32(reader["total"]).ToString();

                reader.Close();
            }
            catch (Exception err)
            {
                return "-1";
            }
            finally
            {
                connect.Close();
            }
            return type_user;
        }
        public int getUserIDByUserName(string user)
        {
            int result = 0;
            try
            {
                connect.Open();
                string selectSQL = "SELECT user_id FROM Users WHERE user_name = '" + user.ToString() + "'";
                SqlCommand cmd = new SqlCommand(selectSQL, connect);

                SqlDataReader reader;
                reader = cmd.ExecuteReader();
                if (reader.Read())
                    result = Convert.ToInt32(reader["user_id"]);

                reader.Close();
            }
            catch (Exception err)
            {
                return 0;
            }
            finally
            {
                connect.Close();
            }

            return result;
        }
    }
}
